<html lang="en-US">

<head>
    
<title>golang中使用excelize导出表格 - 花落雨忧</title>

<meta property="og:title" content="golang中使用excelize导出表格 - 花落雨忧">



    



    
    <meta property="description" content="针对golang的几款兼容性好的excel包有tealeg, excelize, openxlsx等，对比语法，选择了excelize
[&amp;hellip;] 方式一： 在go.mod文件中，填写 github.com/xuri/excelize/v2 v2.6.1 ，然后执行 go mod tidy 方式二：在终端输入go get github.com/xuri/excelize/v2 &amp;hellip;">
    <meta property="og:description" content="针对golang的几款兼容性好的excel包有tealeg, excelize, openxlsx等，对比语法，选择了excelize
[&amp;hellip;] 方式一： 在go.mod文件中，填写 github.com/xuri/excelize/v2 v2.6.1 ，然后执行 go mod tidy 方式二：在终端输入go get github.com/xuri/excelize/v2 &amp;hellip;">
    






<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width,initial-scale=1">

<link rel="shortcut icon" href="https://www.lican.asia/logo/logo.png" type="image/x-icon" />



<link rel="stylesheet" href="/css/style.min.css" />

<link rel="stylesheet" href="/css/reset.min.css" />




<script src="https://www.lican.asia/js/highlight.min.js"></script>

<script>
hljs.configure({ ignoreUnescapedHTML: true })
hljs.highlightAll();
</script>


<script src="https://www.lican.asia/js/jquery.min.js"></script>




<link href="https://www.lican.asia/css/hugo-code.min.css" rel="stylesheet" />



    <style>
        .post-content img {
            max-width: 400px;
        }
    </style>
</head>

<body id="period" class="home blog">
    <a class="skip-content" href="#main">Press "Enter" to skip to content</a>
    <div id="overflow-container" class="overflow-container">
        <header class="site-header" id="site-header" role="banner">
    <div class="max-width">
        <div id="title-container" class="title-container">
            <div id="site-title" class="site-title"><a href="/">花落雨忧</a></div>
            <p class="tagline">思所及 力所行 方为真.</p>
        </div>
        
        <div id="menu-primary-container" class="menu-primary-container">
            <div class="icon-container">
            </div>
            <div id="menu-primary" class="menu-container menu-primary" role="navigation">
                <nav class="menu">
                    <ul id="menu-primary-items" class="menu-primary-items">
                        
                        
                        <li id="menu-item-0"
                            class="menu-item menu-item-type-custom menu-item-object-custom ">
                            <a href="/posts" aria-current="page" tabindex="0">首页</a></li>
                        
                        <li id="menu-item-1"
                            class="menu-item menu-item-type-custom menu-item-object-custom ">
                            <a href="/tech/" aria-current="page" tabindex="1">技术文档</a></li>
                        
                        <li id="menu-item-2"
                            class="menu-item menu-item-type-custom menu-item-object-custom ">
                            <a href="/article/" aria-current="page" tabindex="2">文章</a></li>
                        
                        <li id="menu-item-3"
                            class="menu-item menu-item-type-custom menu-item-object-custom ">
                            <a href="/project/" aria-current="page" tabindex="3">项目</a></li>
                        
                        <li id="menu-item-4"
                            class="menu-item menu-item-type-custom menu-item-object-custom ">
                            <a href="/about/" aria-current="page" tabindex="4">关于</a></li>
                        
                    </ul>
                </nav>
            </div>
        </div>
    </div>
</header>

        <div id="primary-container" class="primary-container">
            <div class="max-width">
                <section id="main" class="main" role="main">
                    <div id="loop-container" class="loop-container">
                        <div
                            class="post type-post status-publish format-standard hentry entry">
                            <article>
                                <div class="post-container">
                                    <div class="post-header">
                                        <h2 class="post-title">
                                            <a href="/posts/golang%E7%9A%84excelize%E4%BD%BF%E7%94%A8%E5%BF%83%E5%BE%97/">golang中使用excelize导出表格</a>
                                        </h2>
                                        
                                        <div class="post-byline">Published on
                                            <a class="date" href="javascript:;">2024/07/19</a>
                                            
                                            
                                            
                                            
                                            
                                        
                                    </div>
                                    <div class="post-content">
                                        <h3 id="golang中使用excelize导出表格">golang中使用excelize导出表格</h3>
<h4 id="背景客户每天定点取一组数据填入excel中记录发酵周期内温度变化并根据公式计算报表值现希望通过系统完成该操作">背景：客户每天定点取一组数据填入excel中，记录发酵周期内温度变化，并根据公式计算报表值，现希望通过系统完成该操作</h4>
<h4 id="分析">分析</h4>
<p>针对golang的几款兼容性好的excel包有tealeg, excelize, openxlsx等，对比语法，选择了excelize</p>
<h5 id="step1-下载安装包">Step1: 下载安装包</h5>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-go" data-lang="go"><span style="display:flex;"><span><span style="color:#a6e22e">方式一</span><span style="color:#960050;background-color:#1e0010">：</span> <span style="color:#a6e22e">在go</span>.<span style="color:#a6e22e">mod文件中</span><span style="color:#960050;background-color:#1e0010">，</span><span style="color:#a6e22e">填写</span> <span style="color:#a6e22e">github</span>.<span style="color:#a6e22e">com</span><span style="color:#f92672">/</span><span style="color:#a6e22e">xuri</span><span style="color:#f92672">/</span><span style="color:#a6e22e">excelize</span><span style="color:#f92672">/</span><span style="color:#a6e22e">v2</span> <span style="color:#a6e22e">v2</span><span style="color:#ae81ff">.6.1</span> <span style="color:#960050;background-color:#1e0010">，</span><span style="color:#a6e22e">然后执行</span> <span style="color:#66d9ef">go</span> <span style="color:#a6e22e">mod</span> <span style="color:#a6e22e">tidy</span>
</span></span><span style="display:flex;"><span><span style="color:#a6e22e">方式二</span><span style="color:#960050;background-color:#1e0010">：</span><span style="color:#a6e22e">在终端输入go</span> <span style="color:#a6e22e">get</span> <span style="color:#a6e22e">github</span>.<span style="color:#a6e22e">com</span><span style="color:#f92672">/</span><span style="color:#a6e22e">xuri</span><span style="color:#f92672">/</span><span style="color:#a6e22e">excelize</span><span style="color:#f92672">/</span><span style="color:#a6e22e">v2</span> 
</span></span></code></pre></div><h5 id="step2-查询数据">Step2: 查询数据</h5>
<p>根据客户要求，查询窖池中固定时间点的一条数据，分上中下层，当设备未上报该时间点数据时，取时间点前后教近的一条，该步骤省略&hellip;</p>
<h5 id="step3-处理表格">Step3: 处理表格</h5>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-go" data-lang="go"><span style="display:flex;"><span><span style="color:#75715e">// 创建一个新的Excel文件
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#a6e22e">f</span> <span style="color:#f92672">:=</span> <span style="color:#a6e22e">excelize</span>.<span style="color:#a6e22e">NewFile</span>()
</span></span></code></pre></div><blockquote>
<p>注意，同一个excel单元格只能设置一次样式，多次设置会覆盖</p>
</blockquote>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-go" data-lang="go"><span style="display:flex;"><span><span style="color:#75715e">// 如果一个单元格的样式较多，可以把样式一次性加上
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#a6e22e">styleID</span>, <span style="color:#a6e22e">_</span> <span style="color:#f92672">:=</span> <span style="color:#a6e22e">f</span>.<span style="color:#a6e22e">NewStyle</span>(<span style="color:#e6db74">`{
</span></span></span><span style="display:flex;"><span><span style="color:#e6db74">		&#34;font&#34;: {
</span></span></span><span style="display:flex;"><span><span style="color:#e6db74">			&#34;size&#34;:12,
</span></span></span><span style="display:flex;"><span><span style="color:#e6db74">			&#34;bold&#34;: true
</span></span></span><span style="display:flex;"><span><span style="color:#e6db74">		},
</span></span></span><span style="display:flex;"><span><span style="color:#e6db74">		&#34;alignment&#34;: {
</span></span></span><span style="display:flex;"><span><span style="color:#e6db74">			&#34;horizontal&#34;: &#34;center&#34;,
</span></span></span><span style="display:flex;"><span><span style="color:#e6db74">			&#34;vertical&#34;: &#34;center&#34;
</span></span></span><span style="display:flex;"><span><span style="color:#e6db74">		}
</span></span></span><span style="display:flex;"><span><span style="color:#e6db74">	}`</span>)
</span></span><span style="display:flex;"><span><span style="color:#a6e22e">f</span>.<span style="color:#a6e22e">SetCellStyle</span>(<span style="color:#e6db74">&#34;Sheet1&#34;</span>, <span style="color:#e6db74">&#34;A1&#34;</span>, <span style="color:#e6db74">&#34;A1&#34;</span>, <span style="color:#a6e22e">styleColor</span>) <span style="color:#75715e">// 设置样式
</span></span></span></code></pre></div><div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-go" data-lang="go"><span style="display:flex;"><span><span style="color:#75715e">// 合并单元格
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#a6e22e">f</span>.<span style="color:#a6e22e">MergeCell</span>(<span style="color:#e6db74">&#34;Sheet1&#34;</span>, <span style="color:#e6db74">&#34;A1&#34;</span>, <span style="color:#e6db74">&#34;A2&#34;</span>)
</span></span><span style="display:flex;"><span><span style="color:#75715e">// 设置单元格值
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#a6e22e">f</span>.<span style="color:#a6e22e">SetCellValue</span>(<span style="color:#e6db74">&#34;Sheet1&#34;</span>, <span style="color:#e6db74">&#34;A1&#34;</span>, <span style="color:#a6e22e">OperationName</span>)
</span></span><span style="display:flex;"><span><span style="color:#75715e">// 设置行高
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#a6e22e">f</span>.<span style="color:#a6e22e">SetRowHeight</span>(<span style="color:#e6db74">&#34;Sheet1&#34;</span>, <span style="color:#e6db74">&#34;A1&#34;</span>, <span style="color:#ae81ff">22.0</span>)
</span></span></code></pre></div><div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-go" data-lang="go"><span style="display:flex;"><span><span style="color:#75715e">// 表格的填充需根据excel的横纵两个轴循环写入 eg:
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>	<span style="color:#a6e22e">columnIndex</span> = <span style="color:#ae81ff">2</span>
</span></span><span style="display:flex;"><span>	<span style="color:#66d9ef">for</span> <span style="color:#a6e22e">_</span>, <span style="color:#a6e22e">device</span> <span style="color:#f92672">:=</span> <span style="color:#66d9ef">range</span> <span style="color:#a6e22e">data</span> { <span style="color:#75715e">// 循环填入数据
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>		<span style="color:#a6e22e">rowIndex</span> <span style="color:#f92672">:=</span> <span style="color:#ae81ff">4</span>
</span></span><span style="display:flex;"><span>		<span style="color:#66d9ef">for</span> <span style="color:#a6e22e">_</span>, <span style="color:#a6e22e">dataGroup</span> <span style="color:#f92672">:=</span> <span style="color:#66d9ef">range</span> <span style="color:#a6e22e">device</span>.<span style="color:#a6e22e">Data</span> {
</span></span><span style="display:flex;"><span>			<span style="color:#a6e22e">lie1</span> <span style="color:#f92672">:=</span> <span style="color:#a6e22e">e</span>.<span style="color:#a6e22e">GetColumnLetter</span>(<span style="color:#a6e22e">columnIndex</span>)
</span></span><span style="display:flex;"><span>			<span style="color:#a6e22e">lie2</span> <span style="color:#f92672">:=</span> <span style="color:#a6e22e">e</span>.<span style="color:#a6e22e">GetColumnLetter</span>(<span style="color:#a6e22e">columnIndex</span> <span style="color:#f92672">+</span> <span style="color:#ae81ff">1</span>)
</span></span><span style="display:flex;"><span>			<span style="color:#66d9ef">for</span> <span style="color:#a6e22e">_</span>, <span style="color:#a6e22e">item</span> <span style="color:#f92672">:=</span> <span style="color:#66d9ef">range</span> <span style="color:#a6e22e">dataGroup</span> {
</span></span><span style="display:flex;"><span>				<span style="color:#a6e22e">t</span> <span style="color:#f92672">:=</span> <span style="color:#a6e22e">time</span>.<span style="color:#a6e22e">Unix</span>(<span style="color:#a6e22e">item</span>.<span style="color:#a6e22e">CreatedTime</span>, <span style="color:#ae81ff">0</span>)
</span></span><span style="display:flex;"><span>				<span style="color:#a6e22e">timeStr</span> <span style="color:#f92672">:=</span> <span style="color:#a6e22e">t</span>.<span style="color:#a6e22e">Format</span>(<span style="color:#e6db74">&#34;01月02日 15:04&#34;</span>)
</span></span><span style="display:flex;"><span>				<span style="color:#a6e22e">f</span>.<span style="color:#a6e22e">SetCellValue</span>(<span style="color:#e6db74">&#34;Sheet1&#34;</span>, <span style="color:#a6e22e">fmt</span>.<span style="color:#a6e22e">Sprintf</span>(<span style="color:#e6db74">&#34;%s%d&#34;</span>, <span style="color:#a6e22e">lie1</span>, <span style="color:#a6e22e">rowIndex</span>), <span style="color:#a6e22e">timeStr</span>)
</span></span><span style="display:flex;"><span>				<span style="color:#a6e22e">f</span>.<span style="color:#a6e22e">SetCellValue</span>(<span style="color:#e6db74">&#34;Sheet1&#34;</span>, <span style="color:#a6e22e">fmt</span>.<span style="color:#a6e22e">Sprintf</span>(<span style="color:#e6db74">&#34;%s%d&#34;</span>, <span style="color:#a6e22e">lie2</span>, <span style="color:#a6e22e">rowIndex</span>), <span style="color:#a6e22e">item</span>.<span style="color:#a6e22e">Value</span>)
</span></span><span style="display:flex;"><span>				<span style="color:#a6e22e">f</span>.<span style="color:#a6e22e">SetCellStyle</span>(<span style="color:#e6db74">&#34;Sheet1&#34;</span>, <span style="color:#a6e22e">fmt</span>.<span style="color:#a6e22e">Sprintf</span>(<span style="color:#e6db74">&#34;%s%d&#34;</span>, <span style="color:#a6e22e">lie2</span>, <span style="color:#a6e22e">rowIndex</span>), <span style="color:#a6e22e">fmt</span>.<span style="color:#a6e22e">Sprintf</span>(<span style="color:#e6db74">&#34;%s%d&#34;</span>, <span style="color:#a6e22e">lie2</span>, <span style="color:#a6e22e">rowIndex</span>), <span style="color:#a6e22e">styleCenter</span>)
</span></span><span style="display:flex;"><span>			}
</span></span><span style="display:flex;"><span>			<span style="color:#a6e22e">rowIndex</span><span style="color:#f92672">++</span>
</span></span><span style="display:flex;"><span>		}
</span></span><span style="display:flex;"><span>		<span style="color:#a6e22e">columnIndex</span> <span style="color:#f92672">+=</span> <span style="color:#ae81ff">2</span> <span style="color:#75715e">// 移动到下一列
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>	}
</span></span></code></pre></div><h5 id="step3-保存文件">Step3: 保存文件</h5>
<p>excel表格数据填充完毕后，保存文件</p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-go" data-lang="go"><span style="display:flex;"><span><span style="color:#75715e">// 保存文件
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>	<span style="color:#a6e22e">FileName</span> <span style="color:#f92672">:=</span> <span style="color:#e6db74">&#34;langjiu_pit.xlsx&#34;</span>
</span></span><span style="display:flex;"><span>	<span style="color:#a6e22e">f</span>.<span style="color:#a6e22e">SaveAs</span>(<span style="color:#a6e22e">FileName</span>)
</span></span><span style="display:flex;"><span><span style="color:#75715e">// 执行后，可看到根目录有一个名为langjiu_pit.xlsx的表格
</span></span></span></code></pre></div><h5 id="step4-导出表格">Step4: 导出表格</h5>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-go" data-lang="go"><span style="display:flex;"><span><span style="color:#75715e">// // 设置HTTP响应头，指定文件名和Content-Type
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>	<span style="color:#a6e22e">co</span>.<span style="color:#a6e22e">Header</span>(<span style="color:#e6db74">&#34;Content-Disposition&#34;</span>, <span style="color:#e6db74">&#34;attachment; filename=&#34;</span><span style="color:#f92672">+</span><span style="color:#a6e22e">FileName</span>)
</span></span><span style="display:flex;"><span>	<span style="color:#a6e22e">co</span>.<span style="color:#a6e22e">Header</span>(<span style="color:#e6db74">&#34;Content-Type&#34;</span>, <span style="color:#e6db74">&#34;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&#34;</span>)
</span></span><span style="display:flex;"><span>	<span style="color:#75715e">// 打开文件
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>	<span style="color:#a6e22e">file</span>, <span style="color:#a6e22e">err</span> <span style="color:#f92672">:=</span> <span style="color:#a6e22e">os</span>.<span style="color:#a6e22e">Open</span>(<span style="color:#a6e22e">FileName</span>) <span style="color:#75715e">// 替换为你的文件路径
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>	<span style="color:#66d9ef">if</span> <span style="color:#a6e22e">err</span> <span style="color:#f92672">!=</span> <span style="color:#66d9ef">nil</span> {
</span></span><span style="display:flex;"><span>		<span style="color:#66d9ef">return</span>
</span></span><span style="display:flex;"><span>	}
</span></span><span style="display:flex;"><span>	<span style="color:#66d9ef">defer</span> <span style="color:#a6e22e">file</span>.<span style="color:#a6e22e">Close</span>()
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span>	<span style="color:#75715e">// 发送文件内容
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>	<span style="color:#a6e22e">_</span>, <span style="color:#a6e22e">err</span> = <span style="color:#a6e22e">io</span>.<span style="color:#a6e22e">Copy</span>(<span style="color:#a6e22e">co</span>.<span style="color:#a6e22e">Writer</span>, <span style="color:#a6e22e">file</span>) <span style="color:#75715e">// 注意：这里使用了io.Copy，但没有在示例中导入&#34;io&#34;包
</span></span></span></code></pre></div><h5 id="step5-查看表格">Step5: 查看表格</h5>
<p><img src="https://www.lican.site/images/1721377152631.png" alt="1721377152631"></p>


                                        
                                        
                                        
                                        
                                    </div>

                                    
                                    
                                    

                                    
                                    <div class="post-meta">
                                        
                                        
                                        
                                        <nav class="further-reading">
                                            
                                            <div class="previous">
                                                <span>&lt;&lt; Prev</span>
                                                <a href="https://www.lican.asia/tech/golang%E7%9A%84excelize%E4%BD%BF%E7%94%A8%E5%BF%83%E5%BE%97/"
                                                    rel="prev">golang中使用excelize导出表格</a> </div>
                                            
                                            
                                            <div class="next">
                                                <span>Next >></span>
                                                <a href="https://www.lican.asia/article/%E7%88%B6%E4%BA%B2%E7%9A%84%E7%97%85/">父亲的病</a> 
                                            </div>
                                            
                                        </nav>
                                    </div>
                                    
                                    

                                    
                                    
                                    

                                </div>
                            </article>
                        </div>
                    </div>
                </section>
                <aside class="sidebar sidebar-primary" id="sidebar-primary" role="complementary">
    <h1 class="screen-reader-text">Sidebar</h1>
    

    
    
    <section id="text-2" class="widget widget_text">
        <div class="textwidget">
            
            <div id="profile">
                <div id="profile_picture"><img src="https://www.lican.asia/logo/logo.png"></div>
                <div id="profile_intro">
                    <p><span class="name">Lican</span></p>
                    <p class="intro">全栈开发者，爱好造轮子。</p>
                </div>
            </div>
            
            <p>
                <script type="text/javascript">
                    (function ($) {
                        $(document).ready(function () {
                            var menuPrimaryContainer = $('#menu-primary-container');
                            var profile = $('#text-2');
                            $('#toggle-navigation').click(function () {
                                if (menuPrimaryContainer.hasClass('open')) {
                                    profile.removeClass('open');
                                } else {
                                    profile.addClass('open');
                                }
                            });
                        });
                    })(jQuery);
                </script>
            </p>
        </div>
    </section>
    
    
    
    
    
    <section id="text-5" class="widget widget_text">
        <h2 class="widget-title">开源项目</h2>
        <div class="textwidget">
            <div id="projects" style="line-height: 22px;">
                
                <a href="https://github.com/idoubi/gonews"
                    target="_blank">gonews</a>: &nbsp;Daily news for golang<br>
                
                <a href="https://github.com/idoubi/sql2struct"
                    target="_blank">sql2struct</a>: &nbsp;Generate go struct according to SQL<br>
                
                <a href="https://github.com/idoubi/goz"
                    target="_blank">goz</a>: &nbsp;Request library used in golang<br>
                
        </div>
    </section>
    
    

    
    
    
    
    <section id="qrcode" class="widget widget_media_image">
        <h2 class="widget-title">微信公众号</h2>
        <img width="258" height="258"
            src="https://www.lican.asia/wechat/lican.png"
            class="image wp-image-5514  attachment-full size-full" alt=""
            style="max-width: 100%; height: auto;"
            sizes="(max-width: 258px) 100vw, 258px">
    </section>
    
    

    
    
    

    
    
    

    
    
    
    
</aside>
            </div>
        </div>

        <footer id="site-footer" class="site-footer" role="contentinfo">
    <div class="max-width">
    </div>
    <div class="footer">
        <div id="footercontent">
            © lican.asia All rights reserved<br/>
            Built with Hugo Theme <a href="https://github.com/idoubi/hugo-theme-period" target="_blank">Period</a>
        </div>
    </div>
</footer>

<script>
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?e8351b6d4626d5881d439ea1f6184baa";
      var s = document.getElementsByTagName("script")[0]; 
      s.parentNode.insertBefore(hm, s);
    })();
</script>
    
    
    </div>
    
</body>

</html>